<?php
include_once('../includes/definer.php');
include_once(INC.'php/bootstrap.php');
$orderColumn = array(
    'p.titulo',
    'c.value',
    'e.value',
    'p.created_at',
    'p.updated_at',
    'visitas',
    'p.id'
);
$desde = $_GET['length'] * $_GET['start'];

$recordsTotal = Doctrine_Query::create()
        ->select('count(p.id)')
        ->from('Publicacion p')
        ->innerJoin('p.categoria c')
        ->where('c.slug <> "prensa" and c.slug <> "about-me"')
        ->andWhere('p.id_estado <> '.Estado::BORRADO)
        ->execute(array(), Doctrine::HYDRATE_SINGLE_SCALAR)
;
//<a href=\"producto/", p.slug, "\" class=\"btn btn-primary\"><i class=\"fa fa-pencil\"></i></a>
$data = Doctrine_Query::create()
        /*->select('
            p.id,
            CONCAT("row", p.id) as DT_RowId,
            CONCAT("
                <a href=\"#\" data-id=\"",p.id,"\" class=\"btn btn-danger borrarPublicacion\"><i class=\"fa fa-trash-o\"></i></a>
                <a href=\"publicacion/",p.slug,"\" class=\"btn btn-primary\"><i class=\"fa fa-pencil\"></i></a>"
            ) as acciones,
            CONCAT("<a href=\"#\" class=\"tituloEditable\" data-value=\"",p.titulo,"\" data-publicacion=\"",p.id,"\" data-type=\"text\">",p.titulo,"</a>") as titulo,
            CONCAT("<a href=\"#\" class=\"fechaEditable\" data-placement=\"right\" data-original-title=\"Modificar fecha\" data-publicacion=\"",p.id,"\" data-type=\"date\">",DATE_FORMAT(p.fecha, "%d-%m-%Y"),"</a>") as fecha,
            CONCAT("<a href=\"#\" class=\"categoriaEditable\" data-value=\"",c.id,"\" data-publicacion=\"",p.id,"\" data-type=\"select\">",c.value,"</a>") as categoria,
            CONCAT("<a href=\"#\" class=\"estadoEditable\" data-value=\"",e.id,"\" data-publicacion=\"",p.id,"\" data-type=\"select\">",e.value,"</a>") as estado,
            p.visitas as visitas
        ')*/
        ->select('
            p.id,
            CONCAT("row", p.id) as DT_RowId,
            CONCAT("
                <a href=\"#\" data-id=\"",p.id,"\" class=\"btn btn-danger borrarPublicacion\"><i class=\"fa fa-trash-o\"></i></a>
                <a href=\"publicacion/",p.slug,"\" class=\"btn btn-primary\"><i class=\"fa fa-pencil\"></i></a>"
            ) as acciones,
            p.titulo as titulo,
            CONCAT("<a href=\"#\" class=\"categoriaEditable\" data-value=\"",c.id,"\" data-publicacion=\"",p.id,"\" data-type=\"select\">",c.value,"</a>") as categoria,
            CONCAT("<a href=\"#\" class=\"estadoEditable\" data-value=\"",e.id,"\" data-publicacion=\"",p.id,"\" data-type=\"select\">",e.value,"</a>") as estado,
            DATE_FORMAT(p.created_at, "%d-%m-%Y") as created,
            DATE_FORMAT(p.updated_at, "%d-%m-%Y") as updated,
            count(v.id) as visitas
        ')
        ->from('Publicacion p')
        ->innerJoin('p.categoria c')
        ->innerJoin('p.estado e WITH e.id <> ?', Estado::BORRADO)
        ->leftJoin('p.visitas v')
        ->where('c.slug <> "prensa" and c.slug <> "about-me"')
        ->limit($_GET['length'])
        ->offset($_GET['start'])
        ->groupBy('p.id')
        ->orderBy($orderColumn[$_GET['order'][0]['column']].' '.$_GET['order'][0]['dir'])
;


$recordsFiltered = Doctrine_Query::create()
        ->select('count(p.id)')
        ->from('Publicacion p')
        ->innerJoin('p.categoria c')
        ->where('c.slug <> "prensa" and c.slug <> "about-me"')
        ->andWhere('p.id_estado <> '.Estado::BORRADO)
        
;
//busqueda
if ($_GET['search']['value']) {
    $data->andWhere('p.titulo like "%'.$_GET['search']['value'].'%"');
    $recordsFiltered->andWhere('p.titulo like "%'.$_GET['search']['value'].'%"');
}
//fin busqueda
$recordsFiltered = $recordsFiltered->execute(array(), Doctrine::HYDRATE_SINGLE_SCALAR);

//echo(json_encode($productos)); exit();
$restul = array(
    'recordsTotal'=>$recordsTotal,
    'recordsFiltered'=>$recordsFiltered,
    'data'=>$data->execute(array(), Doctrine::HYDRATE_ARRAY)
);
echo(json_encode($restul));
?>